import xlrd


def analyze_excel_data(file_path):
    try:
        # 打开Excel文件
        workbook = xlrd.open_workbook(file_path)
        sheet = workbook.sheet_by_index(0)

        # a) 统计表格中有多少人（减去表头行）
        total_people = sheet.nrows - 1
        print(f"a) 表格中总人数: {total_people}")

        # 初始化计数器
        telecom_count = 0
        unicom_count = 0
        mobile_count = 0
        male_count = 0
        female_count = 0
        old_employee_count = 0
        high_salary_count = 0
        low_salary_count = 0
        media_company_count = 0
        high_risk_area_count = 0

        # 列索引映射（根据实际Excel调整）
        # 假设列: 0序号 1工牌号 2花名 3真实姓名 4密码 5电话号码 6邮箱 7年龄 8性别 9居住地址 10入职日期 11薪资 12职责 13外包公司
        PHONE_COL = 5  # 电话号码列
        GENDER_COL = 8  # 性别列
        AGE_COL = 7  # 年龄列
        SALARY_COL = 11  # 薪资列
        COMPANY_COL = 13  # 外包公司列
        ADDRESS_COL = 9  # 居住地址列

        # 遍历数据行（从第2行开始，跳过表头）
        for row in range(1, sheet.nrows):
            # 获取当前行数据
            phone = sheet.cell_value(row, PHONE_COL) or ''
            gender = sheet.cell_value(row, GENDER_COL) or ''
            age = sheet.cell_value(row, AGE_COL)
            salary = sheet.cell_value(row, SALARY_COL)
            company = sheet.cell_value(row, COMPANY_COL) or ''
            address = sheet.cell_value(row, ADDRESS_COL) or ''

            # 处理手机号为数值类型的情况
            if isinstance(phone, float):
                phone = str(int(phone))

            # b) 统计运营商用户数量（根据手机号段判断）
            if isinstance(phone, str) and len(phone) >= 3:
                prefix = phone[:3]
                telecom_prefixes = {'133', '153', '177', '180', '181', '189'}
                unicom_prefixes = {'130', '131', '132', '155', '156', '185', '186'}
                mobile_prefixes = {'134', '135', '136', '137', '138', '139', '150', '151', '152', '157', '158', '159',
                                   '182', '183', '184', '187', '188'}

                if prefix in telecom_prefixes:
                    telecom_count += 1
                elif prefix in unicom_prefixes:
                    unicom_count += 1
                elif prefix in mobile_prefixes:
                    mobile_count += 1

            # c) 统计男女人数
            if gender == '男':
                male_count += 1
            elif gender == '女':
                female_count += 1

            # d) 统计年龄超过45岁的员工
            if isinstance(age, (int, float)) and age > 45:
                old_employee_count += 1

            # e) 统计高薪和底薪人员
            if isinstance(salary, (int, float)):
                if salary > 8000:
                    high_salary_count += 1
                elif salary < 3000:
                    low_salary_count += 1

            # f) 统计传媒公司人员
            if '传媒' in company:
                media_company_count += 1

            # g) 统计高危地区人数
            high_risk_regions = {'黑龙江', '北京', '福建', '四川'}
            if any(region_name in address for region_name in high_risk_regions):
                high_risk_area_count += 1

        # 计算占比并输出结果
        print("\n统计结果：")
        print(f"a) 总人数: {total_people}")

        if total_people > 0:
            telecom_pct = telecom_count / total_people * 100
            unicom_pct = unicom_count / total_people * 100
            mobile_pct = mobile_count / total_people * 100
            print(f"b) 电信用户: {telecom_count} ({telecom_pct:.2f}%)")
            print(f"   联通用户: {unicom_count} ({unicom_pct:.2f}%)")
            print(f"   移动用户: {mobile_count} ({mobile_pct:.2f}%)")

        print(f"c) 男性人数: {male_count}, 女性人数: {female_count}")
        print(f"d) 年龄超过45岁: {old_employee_count}人")
        print(f"e) 薪资>8000元: {high_salary_count}人, 薪资<3000元: {low_salary_count}人")
        print(f"f) 传媒公司人员: {media_company_count}人")
        print(f"g) 高危地区人数: {high_risk_area_count}人")

    except FileNotFoundError:
        print(f"错误：文件 '{file_path}' 不存在")
    except Exception as e:
        print(f"错误：{str(e)}")


if __name__ == "__main__":
    file_path = "员工信息表.xls"  # 替换为你的Excel文件路径
    analyze_excel_data(file_path)